/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.jsepc.rep.report;

import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.demension.CheckLeaf;
import cn.com.jsepc.rep.vo.FactTableVO;
import cn.com.jsepc.rep.vo.FieldDataVO;
import cn.com.jsepc.rep.vo.RepDataVO;
import cn.com.jsepc.rep.vo.RepDemensionVO;
import com.jarp.db.DAO;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Set;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 *
 * @author 赵远
 */
public class FillDataSQ_Wan {

    public static String DemIsNotSelect = "99999";

    public String getDataGrid(String start, String limit, String repid, String corpid, String ishow) {
        String res = "";
        boolean isshow = Boolean.valueOf(ishow);
        DAO dao = new DAO();
        JSONArray json = new JSONArray();
        String totalCount = null;
        try {
            //获取事实表表名
            String facttable = dao.getDataValue(ConstUtil.DBNAME, "SELECT t.REFFACT from tb_report t WHERE t.REPID=" + repid);
            //获取上期值时间
            String lastTime = getSQ(repid);
            //获取本期值
            String thistime = getThis(repid);
            //获取时间字段
            String yearcol = new FillDataSQ().getTimeCol(repid);
            //获取维度列表,即上期值所筛选的维度
            String demJsonArr = this.getDemlist(facttable, repid, lastTime, yearcol, this.getThis(repid), isshow);
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart / iPageSize + 1;
            StringBuffer demwhereBuffer = new StringBuffer();
            if (demJsonArr != null && !demJsonArr.equals("")) {
                JSONObject qryjson = JSONObject.fromString(demJsonArr);
                JSONArray demArr = qryjson.getJSONArray("demlist");
                for (int i = 0; i < demArr.length(); i++) {
                    JSONObject jsonDem = demArr.getJSONObject(i);
                    String facdimtable = jsonDem.getString("facdimtable");
                    String facdimcodecol = jsonDem.getString("facdimcodecol");
                    String facdimvalue = jsonDem.getString("facdimvalue");
                    if (!facdimvalue.equals(this.DemIsNotSelect)) {
                        demwhereBuffer.append("\n and " + facdimtable + "." + facdimcodecol + " in (" + facdimvalue + ")");
                    }
                }
            } else {
                System.out.println("demJsonArr is null");
            }
            RepDataVO repVo = getRepDemension(repid);
            if (repVo != null) {
                String tablename = repVo.getReffact(); //TF_REP_HRCNT
                String faccol = repVo.getRepvaluecol(); //HTCNT
                String facsumcol = "SUMVAL";
                String dwDimCodeCol = null;
                String dwDimCodeVal = null;
                boolean bDwDim = false;
                ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
                String timecolid = "";    //时间字段的id
                String timecolname = "";  //时间字段的name
                if (tablename != null && faccol != null && repDemLst != null && repDemLst.size() > 0) {
                    StringBuffer ssql = new StringBuffer();
                    StringBuffer ssqlTot = new StringBuffer();
                    StringBuffer sdemcol = new StringBuffer();
                    StringBuffer sdemtab = new StringBuffer();
                    StringBuffer sdemon = new StringBuffer();
                    StringBuffer sorder = new StringBuffer();
                    ssql.append("select a." + faccol + ",a." + facsumcol);
                    ssql.append(",a.PREVAL,a.TPREVAL,a.TSUMVAL,a.TREPVAL");
                    for (int j = 0; j < repDemLst.size(); j++) {
                        RepDemensionVO repDemVO = repDemLst.get(j);
                        String dimtable = repDemVO.getDimtable(); //td_org
                        String dimidcol = repDemVO.getIdcol();
                        String facdimcol = repDemVO.getFactdimcol();  //ORG_ID
                        String dimcodecol = repDemVO.getCodecol();
                        String dimnamecol = repDemVO.getNamecol();
                        boolean b = this.isSelect(facdimcol, demJsonArr);
                        if (b == true) {
                            ssql.append("\n ,z." + facdimcol);
                            ssql.append("\n ,z." + facdimcol + dimnamecol);
                            if (j != 0) {
                                sdemtab.append(",");
                                sdemon.append(" and ");
                                sorder.append(",");
                                sdemcol.append("\n,");
                            }
                            sdemcol.append("" + dimtable + "." + dimidcol + " as " + facdimcol);
                            sdemcol.append("\n," + dimtable + "." + dimnamecol + " as " + facdimcol + dimnamecol);

                            sdemtab.append(dimtable);
                            sdemon.append(" z." + facdimcol + " = a." + facdimcol);
                            sorder.append(facdimcol);
                            String dimtype = repDemVO.getDemtype();//1单位维度 2时间维度 3其他维度
                            if (dimtype != null) {
                                if (dimtype.equals("1")) {
                                    if (dimcodecol != null && !dimcodecol.equals("")) {
                                        bDwDim = true;
                                        dwDimCodeVal = getDimCodeByID(dimtable, dimidcol, dimcodecol, corpid);
                                        dwDimCodeCol = dimtable + "." + dimcodecol;
                                    }
                                } else if (dimtype.equals("2")) {
                                    timecolid = facdimcol;
                                    timecolname = facdimcol + dimnamecol;
                                }
                            }
                        } else {
                            ssql.append("\n ," + DemIsNotSelect + " as " + facdimcol);
                            ssql.append("\n ," + DemIsNotSelect + " as " + facdimcol + dimnamecol);
                            if (j != 0) {
                                sdemon.append(" and ");
                            }
                            sdemon.append(" a." + facdimcol + "=" + this.DemIsNotSelect);
                        }
                    }
                    ssql.append("\n from (select ");
                    sdemcol = this.clear_char(sdemcol);
                    sdemtab = this.clear_char(sdemtab);
                    sorder = this.clear_char(sorder);
                    ssql.append(sdemcol.toString());
                    ssql.append("\n from " + sdemtab.toString() + " where 1=1 ");
                    if (corpid != null && !corpid.equals("") && bDwDim) {
                        if (dwDimCodeVal != null && !dwDimCodeVal.equals("")) {
                            ssql.append(" and " + dwDimCodeCol + " like '" + dwDimCodeVal + "%'");
                        }
                    }
                    ssql.append(demwhereBuffer.toString());
                    ssql.append("\n ) z ");
                    ssql.append("\n left join " + tablename + " a on " + sdemon);
                    ssql.append("\n order by " + sorder.toString());
                    Vector v = dao.getDataSet(ConstUtil.DBNAMEODS, ssql.toString(), iPage, iPageSize);
                    if (timecolid != null && !timecolid.equals("")) {
                        for (int i = 0; i < v.size(); i++) {
                            Hashtable hash = (Hashtable) v.get(i);
                            hash.put(timecolid, thistime);
                            hash.put(timecolname, this.getTimeColName(repid, thistime));
                            v.set(i, hash);
                        }
                    }
                    if (v != null) {
                        json = JSONArray.fromObject(v);
                    }
                    ssqlTot.append("select count(1) CNT from (" + ssql.toString() + " ) zz ");
                    totalCount = dao.getDataValue(ConstUtil.DBNAMEODS, ssqlTot.toString());
                }
            }
            res = "{totalCount:" + totalCount + ",data:" + json.toString() + "}";
        } catch (Exception e) {
        }
        System.out.println("返回前台的数据是："+res);
        return res;
    }

    public boolean isSelect(String facdimcol, String demJsonArr) {
        DAO dao = new DAO();
        boolean b = true;
        System.out.println("demJsonArr:" + demJsonArr);
        JSONObject qryjson = JSONObject.fromString(demJsonArr);
        JSONArray demArr = qryjson.getJSONArray("demlist");
        for (int i = 0; i < demArr.length(); i++) {
            JSONObject jsonDem = demArr.getJSONObject(i);
            String facdimtable = jsonDem.getString("facdimtable");
            String facdimvalue = jsonDem.getString("facdimvalue");
            String sql = "SELECT t.DIMTABLE FROM tb_demension t WHERE t.FACDIMCOL='" + facdimcol + "'";
            System.out.println("isSelect:" + sql);
            try {
                String value = dao.getDataValue(ConstUtil.DBNAME, sql);
                if (value.equals(facdimtable)) {
                    if (facdimvalue.equals(this.DemIsNotSelect)) {
                        b = false;
                    }
                }
            } catch (ServletException ex) {
                Logger.getLogger(FillDataDao_Wan.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return b;
    }

    public String getDataGrid(String repid, String corpid, String demJsonArr) {
        String res = "";
        RepDataVO repVo = getRepDemension(repid);
        if (repVo != null) {
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            res = tablename;
            FactTableVO.setSql(res);
        }

        return res;
    }

    public RepDataVO getRepDemension(String repid) {
        RepDataVO repVO = new RepDataVO();
        DAO dao = new DAO();
        try {
            String sql = "select * from ( select * from tb_report where repid=" + repid + " and ISVALID=1 ) a"
                    + " left join tb_rep_demension b on b.repid=a.repid"
                    + " left join tb_demension c on c.demid=b.dimid order by b.dimorder";
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                ArrayList<RepDemensionVO> repDemLst = new ArrayList<RepDemensionVO>();
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    RepDemensionVO repDemVO = new RepDemensionVO();
                    repDemVO.setId((String) hashRows.get("ID"));
                    repDemVO.setRepid((String) hashRows.get("REPID"));
                    repDemVO.setDimid((String) hashRows.get("DIMID"));
                    repDemVO.setHirecdesc((String) hashRows.get("HIRECDESC"));
                    repDemVO.setDemname((String) hashRows.get("DEMNAME"));
                    repDemVO.setFactdimcol(((String) hashRows.get("FACDIMCOL")).toUpperCase());
                    repDemVO.setAvailsname((String) hashRows.get("AVAILSNAME"));
                    repDemVO.setDimorder((String) hashRows.get("DIMORDER"));
                    repDemVO.setDemcode((String) hashRows.get("DEMCODE"));
                    repDemVO.setDimtable((String) hashRows.get("DIMTABLE"));
                    repDemVO.setIdcol(((String) hashRows.get("IDCOL")).toUpperCase());
                    repDemVO.setNamecol(((String) hashRows.get("NAMECOL")).toUpperCase());
                    repDemVO.setParentcol(((String) hashRows.get("PARENTCOL")).toUpperCase());
                    repDemVO.setCodecol(((String) hashRows.get("CODECOL")).toUpperCase());
                    repDemVO.setDemtype((String) hashRows.get("DIMTYPE"));
                    repDemVO.setIsvalid((String) hashRows.get("ISVALID"));
                    repDemVO.setLevelcol((String) hashRows.get("LEVELCOL"));
                    repDemLst.add(repDemVO);
                    if (i == v.size() - 1) {
                        repVO.setRepid((String) hashRows.get("REPID"));
                        repVO.setRepcode((String) hashRows.get("REPCODE"));
                        repVO.setRepname((String) hashRows.get("REPNAME"));
                        repVO.setCataid((String) hashRows.get("CATAID"));
                        repVO.setRepdesc((String) hashRows.get("REPDESC"));
                        repVO.setDesc((String) hashRows.get("CALDESC"));
                        repVO.setDataprec((String) hashRows.get("DATAPREC"));
                        repVO.setDataunit((String) hashRows.get("DATAUNIT"));
                        repVO.setDepartment((String) hashRows.get("DEPARTMENT"));
                        repVO.setSourcetype((String) hashRows.get("SOURCETYPE"));
                        repVO.setDatatype((String) hashRows.get("DATATYPE"));
                        repVO.setUpdatetime((String) hashRows.get("UPDATETIME"));
                        repVO.setReffact((String) hashRows.get("REFFACT"));
                        repVO.setRepvaluecol(((String) hashRows.get("REPVALUECOL")).toUpperCase());
                        repVO.setIssum((String) hashRows.get("ISSUM"));
                        repVO.setIsvalid((String) hashRows.get("ISVALID"));
                        repVO.setRepDemLst(repDemLst);
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return repVO;
    }

    public String getDimCodeByID(String dimtablename, String dimidcol, String dimcodecol, String dimidval) {
        String sret = "";
        try {
            DAO dao = new DAO();
            String sql = "select " + dimcodecol + " from " + dimtablename + " where " + dimidcol + "=" + dimidval + "";
            sret = dao.getDataValue(ConstUtil.DBNAMEODS, sql);

        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null, ex);

        }
        return sret;
    }

    public StringBuffer clear_char(StringBuffer bstr) {
        String temp = bstr.toString();
        temp = temp.trim();
        bstr = new StringBuffer();
        bstr.append(temp);
        while (true) {
            if (bstr.charAt(0) == ',') {
                bstr.deleteCharAt(0);
            } else {
                break;
            }
        }
        while (true) {
            if (bstr.charAt(bstr.toString().length() - 1) == ',') {
                bstr.deleteCharAt(bstr.length() - 1);
            } else {
                break;
            }
        }
        return bstr;
    }

    /**
     * 获取上期值，通过筛选事实表里的时间维度，取最大值
     *
     * @param repid
     * @return
     */
    public String getSQ(String repid) {
        String res = "";
        DAO dao = new DAO();
        String YearCol = new FillDataSQ().getTimeCol(repid);
        RepDataVO repVo = getRepDemension(repid);
        ArrayList demlist = new ArrayList();
        if (repVo != null) {
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String facdimcol = repDemVO.getFactdimcol();
                String dimtype = repDemVO.getDemtype();  //1单位维度 2时间维度 3其他维度
                if (dimtype.equals("2")) {
                    YearCol = facdimcol;
                } else {
                    demlist.add(facdimcol);
                }
            }
            try {
                String facttable_sql = "SELECT REFFACT from tb_report t where repid=" + repid;
                String table = dao.getDataValue(ConstUtil.DBNAME, facttable_sql);
                String sql = "select max(" + YearCol + ") from " + table + "  where (";
                for (int i = 0; i < demlist.size() - 1; i++) {
                    sql += demlist.get(i) + "!= 99999 or ";
                }
                sql += demlist.get(demlist.size() - 2) + "!=99999) and  " + YearCol + "!=99999";
                System.out.println(sql);
                String lastime = dao.getDataValue(ConstUtil.DBNAME, sql);
                res = lastime;
            } catch (ServletException ex) {
                Logger.getLogger(FillDataSQ.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        System.out.println("上期值："+res);
        return res;
    }

    /**
     * 判断是否有上期值 返回 no ：没有 返回 ok ：有
     *
     * @param repid
     * @return
     */
    public String hasSQ(String repid) {
        String res = "no";
        try {
            DAO dao = new DAO();
            StringBuffer sql = new StringBuffer();
            String sql0 = "select reffact from tb_report where repid=" + repid;
            String reffact = dao.getDataValue(ConstUtil.DBNAME, sql0);
            RepDataVO repVo = getRepDemension(repid);
            ArrayList othfactdimcol = new ArrayList();
            String SJfactdimcol = "";
            if (repVo != null) {
                ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
                for (int j = 0; j < repDemLst.size(); j++) {
                    RepDemensionVO repDemVO = repDemLst.get(j);
                    String factdimcol = repDemVO.getFactdimcol();
                    String dimtype = repDemVO.getDemtype();  //1单位维度 2时间维度 3其他维度
                    if (dimtype.equals("2")) {
                        SJfactdimcol = factdimcol;
                    } else {
                        othfactdimcol.add(factdimcol);
                    }
                }
            }
            sql.append("select max(" + SJfactdimcol + ") from " + reffact + " where (");
            for (int i = 0; i < othfactdimcol.size() - 1; i++) {
                sql.append(othfactdimcol.get(i) + "=99999 or ");
            }
            sql.append(othfactdimcol.get(othfactdimcol.size() - 2) + "=99999) and " + SJfactdimcol + "<>99999");
            String result = dao.getDataValue(ConstUtil.DBNAME, sql.toString());
            if (result != null && !result.equals("0")) {
                res = "ok";
            }
        } catch (Exception e) {
        }
        return res;
    }

    /**
     * 获取当期值<br/> 新算法：维度的上期值直接用SQL语句筛选，不再判断具体维度 2013.2.5
     *
     * @param repid
     * @return
     */
    public String getThis(String repid) {
        String res = "";
        String lasttime = this.getSQ(repid);
        RepDataVO repVo = getRepDemension(repid);
        String DIMTable = "";
        String IDCOL = "";
        DAO dao = new DAO();
        if (repVo != null) {
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String dimtype = repDemVO.getDemtype();  //1单位维度 2时间维度 3其他维度
                if (dimtype.equals("2")) {
                    DIMTable = repDemVO.getDimtable();
                    IDCOL = repDemVO.getIdcol();
                }
            }
            String sql = "select min(" + IDCOL + ") from " + DIMTable + " where " + IDCOL + ">" + lasttime;
            try {
                res = dao.getDataValue(ConstUtil.DBNAME, sql);
                if (res.equals("0")) {      //标记，在MySQL里结果集是null时会返回0，别的数据库有待测试
                    res = "";
                }
            } catch (ServletException ex) {
                Logger.getLogger(FillDataSQ.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return res;
    }

    /**
     * 获取上期值筛选的所有的维度，为本期值的填报提供参照
     *
     * @return
     */
    private String getDemlist(String facttable, String repid, String yearid, String timecol, String thistime, boolean isshow) {
        String res = "";
        DAO dao = new DAO();
        ArrayList<String> WanDem = this.getWanDem(repid);  //没有勾选的维度
        RepDataVO repVo = getRepDemension(repid);
        if (repVo != null) {
            Vector v = new Vector();
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String facdimcol = repDemVO.getFactdimcol();
                Hashtable hash = new Hashtable();
                boolean iswcz = false;   //标记该维度时候勾选
                for (String str : WanDem) {
                    if (str.equals(facdimcol)) {
                        iswcz = true;
                    }
                }
                try {
                    //设置维度表表名
                    hash.put("facdimtable", dao.getDataValue(ConstUtil.DBNAME, "SELECT t.DIMTABLE FROM tb_demension t WHERE t.FACDIMCOL='" + facdimcol + "'"));
                    //设置事实表维度字段
                    hash.put("facdimcodecol", dao.getDataValue(ConstUtil.DBNAME, "SELECT t.IDCOL from tb_demension t WHERE t.FACDIMCOL='" + facdimcol + "'"));
                    if (iswcz == true) {
                        hash.put("facdimvalue", "99999");
                    } else {
                        //设置事实表关联字段的值
                        if (isshow == true) {
                            hash.put("facdimvalue", getDataAll(facttable, facdimcol, yearid, timecol, thistime));
                        } else {
                            hash.put("facdimvalue", getData(facttable, facdimcol, yearid, timecol, thistime));
                        }
                    }
                } catch (ServletException ex) {
                    Logger.getLogger(FillDataSQ.class.getName()).log(Level.SEVERE, null, ex);
                }
                v.add(hash);
            }
            JSONArray json = JSONArray.fromObject(v);
            res = "{demlist:" + json.toString() + "}";
        }
        return res;
    }

    /**
     * 如果用户没有勾选参照上期值，则采取此方法，该方法是获取本期值的填报界面
     *
     * @return
     */
    private String getData(String table, String facdimcol, String yearid, String timecol, String thistime) {
        String res = "";
        DAO dao = new DAO();
        String sql = "select distinct " + facdimcol + " from " + table + " where " + timecol + "=" + yearid;
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hash = (Hashtable) v.get(i);
                    Set set = hash.keySet();
                    Object[] str = set.toArray();
                    String getDimcol = "SELECT t.IDCOL from tb_demension t WHERE t.FACDIMCOL='" + str[0] + "'";
                    String id_col = dao.getDataValue(ConstUtil.DBNAME, getDimcol);
                    if (id_col.equals("TIME_ID") || id_col.equals("YEARMONTHWEEK_ID")) {
                        res += thistime + ",";
                    } else {
                        res += hash.get(facdimcol) + ",";
                    }
                }
            } else {
                return "";
            }
        } catch (ServletException ex) {
            Logger.getLogger(FillDataSQ.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res.substring(0, res.length() - 1);
    }

    /**
     * 获取没有勾选的维度
     *
     * @return
     */
    public ArrayList getWanDem(String repid) {
        ArrayList wan = new ArrayList();
        try {
            DAO dao = new DAO();
            StringBuffer sql = new StringBuffer();
            String sql0 = "select reffact from tb_report where repid=" + repid;
            String reffact = dao.getDataValue(ConstUtil.DBNAME, sql0);
            RepDataVO repVo = getRepDemension(repid);
            ArrayList<String> demlist = new ArrayList<String>();

            if (repVo != null) {
                ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
                for (int j = 0; j < repDemLst.size(); j++) {
                    RepDemensionVO repDemVO = repDemLst.get(j);
                    demlist.add(repDemVO.getFactdimcol());
                }
            }
            for (String dem : demlist) {
                String Wansql = "select count(1) from " + reffact + " where " + dem + "=99999";
                String Demval = dao.getDataValue(ConstUtil.DBNAME, Wansql);
                if (Integer.valueOf(Demval) > 0) {
                    wan.add(dem);
                }
            }
        } catch (Exception e) {
        }
        return wan;
    }

    /**
     * 如果用户勾选了参照上期值，则采用此方法，该方法是获取上期值，生成的是上期值填报界面（上期值则会在传到前台前转化成当期值）
     *
     * @return
     */
    private String getDataAll(String table, String facdimcol, String yearid, String timecol, String thistime) {
        String res = "";
        DAO dao = new DAO();
        String sql = "select distinct " + facdimcol + " from " + table + " where " + timecol + "=" + yearid;
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hash = (Hashtable) v.get(i);
                    res += hash.get(facdimcol) + ",";
                }
            } else {
                return "";
            }
        } catch (ServletException ex) {
            Logger.getLogger(FillDataSQ.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res.substring(0, res.length() - 1);
    }

    /**
     * 获取维度表Name字段
     *
     * @return
     */
    public String getTimeColName(String repid, String thistime) {
        String res = "";
        if (thistime.equals("")) {
            res = "已填报";
            return res;
        }
        DAO dao = new DAO();
        String idcol = "";
        String namecol = "";
        String table = "";
        RepDataVO repVo = getRepDemension(repid);
        if (repVo != null) {
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String dimtype = repDemVO.getDemtype();  //1单位维度 2时间维度 3其他维度
                if (dimtype.equals("2")) {
                    idcol = repDemVO.getIdcol();
                    namecol = repDemVO.getNamecol();
                    table = repDemVO.getDimtable();
                }
            }
            String sql = "select " + namecol + " from " + table + " where " + idcol + "=" + thistime;
            try {
                res = dao.getDataValue(ConstUtil.DBNAME, sql);
            } catch (ServletException ex) {
                Logger.getLogger(FillDataSQ.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return res;
    }

    //修改
    public synchronized String DatatbUpdate(String repid, String itemJsonInfo) throws ServletException {
        String ret = "";
        try {
            ArrayList<FieldDataVO> lstResults = new ArrayList<FieldDataVO>();
            ArrayList<String> sqls = new ArrayList<String>();
            JSONObject jsonObject = new JSONObject(itemJsonInfo);
            DAO dao = new DAO();
            RepDataVO repVo = getRepDemension(repid);
            if (repVo == null) {
                return null;
            }
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (repDemLst == null || repDemLst.size() == 0) {
                return null;
            }
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
            JSONArray jsonArr = jsonObject.getJSONArray("listData");
            for (int i = 0; i < jsonArr.length(); i++) {
                JSONObject jsonOne = jsonArr.getJSONObject(i);
                StringBuffer strquery = new StringBuffer();
                StringBuffer strupdate = new StringBuffer();
                StringBuffer strupdatewhere = new StringBuffer();
                StringBuffer strinsert = new StringBuffer();
                StringBuffer strinsertval = new StringBuffer();
                String repvalue = jsonOne.getString(faccol);
                if (repvalue == null || repvalue.equals("")) {
                    repvalue = "NULL";
                }
                String facsumval = jsonOne.getString(facsumcol);
                if (facsumval == null || facsumval.equals("")) {
                    facsumval = "NULL";
                }
                strquery.append("select " + faccol + " from " + tablename);
                strupdate.append("update " + tablename + " set " + faccol + "=" + repvalue + " ");
                strupdate.append(" ," + facsumcol + "=" + facsumval);
                strupdate.append(" where ");
                strinsert.append("insert into " + tablename + " ( ");
                for (int j = 0; j < repDemLst.size(); j++) {
                    RepDemensionVO repDemVO = repDemLst.get(j);
                    String dimtable = repDemVO.getDimtable(); //td_org
                    String dimidcol = repDemVO.getFactdimcol();  //ORG_ID
                    String colvalue = jsonOne.getString(dimidcol);
                    if (j != 0) {
                        strupdatewhere.append(" and ");
                        strinsert.append(",");
                        strinsertval.append(",");
                    }
                    strinsert.append(" " + dimidcol);
                    strupdatewhere.append(" " + dimidcol + " = " + colvalue + "");
                    strinsertval.append(" " + colvalue);
                }
                strquery.append("\n where " + strupdatewhere.toString());
                Vector v = dao.getDataSet(ConstUtil.DBNAMEODS, strquery.toString());
                if (v != null && v.size() > 0) {
                    strupdate.append(strupdatewhere.toString());
                    sqls.add(strupdate.toString());
                } else {
                    if (repvalue == null || repvalue.equals("")) {
                        repvalue = "0";
                    }
                    if (facsumval == null || facsumval.equals("")) {
                        facsumval = "0";
                    }
                    strinsert.append(" ," + faccol + "," + facsumcol + ") values (" + strinsertval.toString() + " ," + repvalue + "," + facsumval + ") ");
                    sqls.add(strinsert.toString());
                }
            }
            System.out.println("sqls=" + sqls.toString());
            if (sqls.size() > 0) {
                if (dao.execCommand(ConstUtil.DBNAMEODS, sqls)) {
                    ret = "ok";
                } else {
                    ret = "fail";
                }
            } else {
                ret = "no";
            }

        } catch (RuntimeException e) {
            e.printStackTrace();
        }
        return ret;
    }
}
